﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Windows.Forms;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Data.OleDb;
using System.IO;
using System.Data;
using SDD;
using NPOI.HSSF.UserModel;

namespace SDD
{
    class Taifex
    {
        public static string dataDay = Program.DataDay;

        //public string url1 = "http://www.taifex.com.tw/chinese/3/7_12_1.asp";
        public string url1 = "http://www.taifex.com.tw/chinese/3/7_12_1_tbl.asp";
        //www.twse.com.tw/ch/trading/fund/BFI82U/BFI82U_print.php?begin_date=20110218&end_date=&report_type=day&language=ch
        public string url2 = "http://www.twse.com.tw/ch/trading/fund/BFI82U/BFI82U_print.php?begin_date=" + dataDay + "&end_date=&report_type=day&language=ch";
        public string url3 = "http://www.taifex.com.tw/chinese/3/7_12_3_tbl.asp";
        public string url4 = "http://www.taifex.com.tw/chinese/3/3_1_1_tbl.asp";
        private string FullFileName = Program.FileDir + "未平倉口數與契約金額.xls";
        public string msg = "";

        public void AggregateData()
        {
            // 期交所
            try
            {
                Debug.Write("dataDay:" + dataDay);
                analysisHTML(url1);
                new UpdateData().UpdateDataToGoogleDoc(FullFileName);
            }
            catch
            {
                Console.WriteLine("未平倉口數與契約金額 資料擷取失敗!!");
            }
        }
        /// <summary>
        /// 解析HTML new
        /// </summary>
        /// <param name="url"></param>
        /// <param name="ds"></param>
        public void analysisHTML(string url)
        {
            string context = new cHttp().dopost_UTF8(url);
            WebBrowser wbr = new WebBrowser();
            wbr.Navigate("");

            wbr.ScriptErrorsSuppressed = true;
            wbr.Document.Write("<script type='text/javascript'>window.onerror = function(){};</script>" + context);

            HtmlElementCollection tb = wbr.Document.GetElementsByTagName("TABLE");
            dataDay = tb[0].GetElementsByTagName("SPAN")[1].InnerHtml.Replace("日期", "");
            HtmlElementCollection tr = tb[0].GetElementsByTagName("TR");

            /////////////////////
            //建立工作表
            NPOIComFunction xls = new NPOIComFunction(FullFileName);
            HSSFSheet sheet;
            // 依年月分頁儲存
            string sheetName = dataDay.Split('/')[0] + Convert.ToInt32(dataDay.Split('/')[1]).ToString("00");
            if (xls.Workbook.GetSheetIndex(sheetName) == -1)
            {
                sheet = xls.Workbook.CreateSheet(sheetName);
            }
            else
            {
                sheet = xls.Workbook.GetSheet(sheetName);
            }


            if (sheet.LastRowNum >= 7 && sheet.GetRow(sheet.LastRowNum - 7).GetCell(0).StringCellValue == dataDay)
            {
                Console.WriteLine(dataDay + " 未平倉口數與契約金額 已經存在!!");
                return;
            }

            int r = sheet.LastRowNum;     // 要寫入excel的row順序

            if (r > 0)
                r += 2;

            int tempRowsCnt = 8;    //一份資料有幾列
            int tempColsCnt = 15;    //一份資料有幾行
            for (int i = 0; i < tempRowsCnt; i++)
            {
                sheet.CreateRow(r + i); //建立NewRow
                for (int j = 0; j < tempColsCnt; j++)
                {
                    sheet.GetRow(r + i).CreateCell(j);//建立NewColumn
                }
            }

            // 日期列給顏色
            HSSFCellStyle style1 = xls.Workbook.CreateCellStyle();
            style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index;
            style1.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
            for (int i = 0; i < 50; i++)
            {
                HSSFCell cell0 = sheet.CreateRow(r).CreateCell(i);
                cell0.CellStyle = style1;
                if (i == 0)
                    cell0.SetCellValue(dataDay); // 寫入日期
            }
            // 寫入日期
            //sheet.GetRow(r).CreateCell(0).SetCellValue(dataDay);

            //交易口數與契約金額
            sheet.GetRow(r + 1).GetCell(1).SetCellValue(tr[2].Children[1].InnerText);
            sheet.GetRow(r + 2).GetCell(1).SetCellValue("多方");
            sheet.GetRow(r + 2).GetCell(3).SetCellValue("空方");
            sheet.GetRow(r + 2).GetCell(5).SetCellValue("多空淨額");
            for (int i = 3; i < tempRowsCnt; i++)
            {
                for (int j = 0; j < 7; j++)
                {
                    if (sheet.GetRow(r + i).GetCell(j) == null)
                        sheet.CreateRow(r + i).CreateCell(j).SetCellValue(tr[i + 1].Children[j].InnerText);
                    else
                        sheet.GetRow(r + i).GetCell(j).SetCellValue(tr[i + 1].Children[j].InnerText);
                }
            }
            //未平倉口數與契約金額
            sheet.GetRow(r + 1).GetCell(9).SetCellValue(tr[9].Children[1].InnerText);
            sheet.GetRow(r + 2).GetCell(9).SetCellValue("多方");
            sheet.GetRow(r + 2).GetCell(11).SetCellValue("空方");
            sheet.GetRow(r + 2).GetCell(13).SetCellValue("多空淨額");
            for (int i = 3; i < tempRowsCnt; i++)
            {
                for (int j = 7; j < 14; j++)
                {
                    if (sheet.GetRow(r + i).GetCell(j) == null)
                        sheet.CreateRow(r + i).CreateCell(j + 1).SetCellValue(tr[i + 8].Children[j - 7].InnerText);
                    else
                        sheet.GetRow(r + i).GetCell(j + 1).SetCellValue(tr[i + 8].Children[j - 7].InnerText);
                }
            }

            /**三大法人買賣金額統計表**/

            context = new cHttp().dopost(url2);
            wbr = new WebBrowser();
            wbr.Navigate("");

            wbr.ScriptErrorsSuppressed = true;
            wbr.Document.Write("<script type='text/javascript'>window.onerror = function(){};</script>" + context);

            tb = wbr.Document.GetElementsByTagName("TABLE");
            tr = tb[2].GetElementsByTagName("TR");

            r = sheet.LastRowNum;

            r = r - 5;
            sheet.GetRow(r - 1).CreateCell(17).SetCellValue("三大法人買賣金額統計表");
            sheet.GetRow(r + 1).CreateCell(16).SetCellValue("單位名稱");
            sheet.GetRow(r + 1).CreateCell(17).SetCellValue("買進金額");
            sheet.GetRow(r + 1).CreateCell(18).SetCellValue("賣出金額");
            sheet.GetRow(r + 1).CreateCell(19).SetCellValue("買賣差額");
            r += 2;
            for (int i = 2; i < tr.Count; i++)
            {
                int c = 16;
                for (int j = 0; j < 4; j++)
                {
                    if (sheet.GetRow(r).GetCell(c) == null)
                        sheet.CreateRow(r).CreateCell(c).SetCellValue(tr[i].Children[j].InnerText);
                    else
                        sheet.GetRow(r).GetCell(c).SetCellValue(tr[i].Children[j].InnerText);
                    c++;
                }

                r++;
            }

            /**臺股期貨 交易口數與契約金額**/

            context = new cHttp().dopost_UTF8(url3);
            wbr = new WebBrowser();
            wbr.Navigate("");

            wbr.ScriptErrorsSuppressed = true;
            wbr.Document.Write("<script type='text/javascript'>window.onerror = function(){};</script>" + context);

            tb = wbr.Document.GetElementsByTagName("TABLE");
            tr = tb[1].GetElementsByTagName("TR");

            r = sheet.LastRowNum;

            r = r - 5;
            sheet.GetRow(r - 1).CreateCell(23).SetCellValue("三大法人買賣金額統計表");
            sheet.GetRow(r).CreateCell(22).SetCellValue("交易口數與契約金額");
            sheet.GetRow(r).CreateCell(28).SetCellValue("未平倉餘額");

            sheet.GetRow(r + 1).CreateCell(22).SetCellValue("多方");
            sheet.GetRow(r + 1).CreateCell(24).SetCellValue("空方");
            sheet.GetRow(r + 1).CreateCell(26).SetCellValue("多空淨額");
            sheet.GetRow(r + 1).CreateCell(28).SetCellValue("多方");
            sheet.GetRow(r + 1).CreateCell(30).SetCellValue("空方");
            sheet.GetRow(r + 1).CreateCell(32).SetCellValue("多空淨額");
            sheet.GetRow(r + 2).CreateCell(21).SetCellValue("身份別");
            sheet.GetRow(r + 2).CreateCell(22).SetCellValue("口數");
            sheet.GetRow(r + 2).CreateCell(23).SetCellValue("契約金額");
            sheet.GetRow(r + 2).CreateCell(24).SetCellValue("口數");
            sheet.GetRow(r + 2).CreateCell(25).SetCellValue("契約金額");
            sheet.GetRow(r + 2).CreateCell(26).SetCellValue("口數");
            sheet.GetRow(r + 2).CreateCell(27).SetCellValue("契約金額");
            sheet.GetRow(r + 2).CreateCell(28).SetCellValue("口數");
            sheet.GetRow(r + 2).CreateCell(29).SetCellValue("契約金額");
            sheet.GetRow(r + 2).CreateCell(30).SetCellValue("口數");
            sheet.GetRow(r + 2).CreateCell(31).SetCellValue("契約金額");
            sheet.GetRow(r + 2).CreateCell(32).SetCellValue("口數");
            sheet.GetRow(r + 2).CreateCell(33).SetCellValue("契約金額");
            r += 3;
            for (int i = 3; i < 6; i++)
            {
                int c = 21;
                for (int j = 0; j < tr[i].Children.Count; j++)
                {
                    if (tr[i].Children.Count > 13 && j < 2)
                        continue;

                    if (sheet.GetRow(r).GetCell(c) == null)
                        sheet.CreateRow(r).CreateCell(c).SetCellValue(tr[i].Children[j].InnerText);
                    else
                        sheet.GetRow(r).GetCell(c).SetCellValue(tr[i].Children[j].InnerText);
                    c++;
                }
                //HSSFCell cell0 = sheet.GetRow(r).GetCell(26);
                //cell0.CellStyle = style1;

                r++;
            }

            /**臺股期貨 (TX) 行情表**/
            context = new cHttp().dopost_UTF8(url4);
            wbr = new WebBrowser();
            wbr.Navigate("");

            wbr.ScriptErrorsSuppressed = true;
            wbr.Document.Write("<script type='text/javascript'>window.onerror = function(){};</script>" + context);

            tb = wbr.Document.GetElementsByTagName("TABLE");
            tr = tb[2].GetElementsByTagName("TR");

            r = sheet.LastRowNum;

            r = r - 5;
            sheet.GetRow(r - 1).CreateCell(36).SetCellValue("臺股期貨 (TX) 行情表");
            for (int i = 0; i < 6; i++)
            {
                int c = 35;
                for (int j = 0; j < tr[i].Children.Count; j++)
                {
                    if (sheet.GetRow(r).GetCell(c) == null)
                        sheet.CreateRow(r).CreateCell(c).SetCellValue(tr[i].Children[j].InnerText);
                    else
                        sheet.GetRow(r).GetCell(c).SetCellValue(tr[i].Children[j].InnerText);
                    c++;
                }


                r++;
            }

            /****/
            //讓目前的工作表選為預設
            xls.Workbook.ActiveSheetIndex = xls.Workbook.NumberOfSheets - 1;
            xls.Workbook.SetSelectedTab(xls.Workbook.NumberOfSheets - 1);
            sheet.IsSelected = true;

            // 寫入實體檔案
            xls.WriteToFile();


        }
    }
}
